﻿-- Vision 1.0 
-- 版本 1.0

CREATE TABLE [dbo].[emp_Employees] (
    [EMPCode] [nvarchar](16) NOT NULL,
    [RefCode] [nvarchar](16),
    [LastName] [nvarchar](256),
    [FirstName] [nvarchar](256),
    [Alias] [nvarchar](256),
    [CHNName] [nvarchar](256),
    [Gender] [nvarchar](1),
    [EMPType] [nvarchar](2) NOT NULL,
	[Status] [int] NOT NULL,
    [Email] [nvarchar](256),
	[Mobile]  [nvarchar](32) NULL,
	[ExtensionNo]  [nvarchar](32) NULL,
    [TerminationDate] [datetime],
    [TerminationType] [nvarchar](8),
    [UserName] [nvarchar](256),
    [UserID] [uniqueidentifier],
    [RecordState] [int] NOT NULL,
    [ModifiedBy] [nvarchar](256),
    [ModifiedDate] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](256),
    [CreatedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_dbo.emp_Employees] PRIMARY KEY ([EMPCode])
)

CREATE TABLE [dbo].[emp_ProfilePics] (
    [EMPCode] [nvarchar](16) NOT NULL,
    [ProfileImage] [varbinary](max),
    [ModifiedBy] [nvarchar](256),
    [ModifiedDate] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](256),
    [CreatedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_dbo.emp_ProfilePics] PRIMARY KEY ([EMPCode])
)



CREATE TABLE [dbo].[emp_Companies] (
    [COMCode] [nvarchar](8) NOT NULL,
    [Name] [nvarchar](256) NOT NULL,
    [ParentCOMCode] [nvarchar](8),
    [RecordState] [int] NOT NULL,
    [Order] [int],
    [ModifiedBy] [nvarchar](256),
    [ModifiedDate] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](256),
    [CreatedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_dbo.emp_Companies] PRIMARY KEY ([COMCode])
)
CREATE INDEX [IX_ParentCOMCode] ON [dbo].[emp_Companies]([ParentCOMCode])

CREATE TABLE [dbo].[emp_Departments] (
    [DEPTCode] [nvarchar](8) NOT NULL,
    [Name] [nvarchar](256) NOT NULL,
    [COMCode] [nvarchar](8) NOT NULL,
    [ParentDEPTCode] [nvarchar](8),
    [RecordState] [int] NOT NULL,
    [Order] [int],
    [ModifiedBy] [nvarchar](256),
    [ModifiedDate] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](256),
    [CreatedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_dbo.emp_Departments] PRIMARY KEY ([DEPTCode])
)
CREATE INDEX [IX_COMCode] ON [dbo].[emp_Departments]([COMCode])
CREATE INDEX [IX_ParentDEPTCode] ON [dbo].[emp_Departments]([ParentDEPTCode])

CREATE TABLE [dbo].[emp_Teams] (
    [TEAMCode] [nvarchar](8) NOT NULL,
	[DEPTCode] [nvarchar](8) NOT NULL,
    [Name] [nvarchar](256) NOT NULL,
    [RecordState] [int] NOT NULL,
    [Order] [int],
    [ModifiedBy] [nvarchar](256),
    [ModifiedDate] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](256),
    [CreatedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_dbo.emp_Teams] PRIMARY KEY ([TEAMCode])
)
CREATE INDEX [IX_DEPTCode] ON [dbo].[emp_Teams]([DEPTCode])

CREATE TABLE [dbo].[emp_Positions] (
    [POSCode] [nvarchar](8) NOT NULL,
    [Name] [nvarchar](256) NOT NULL,
	[Grading] [decimal](18,2) NOT NULL,
	[ParentPOSCode] [nvarchar](8),
    [RecordState] [int] NOT NULL,
    [Order] [int],
    [ModifiedBy] [nvarchar](256),
    [ModifiedDate] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](256),
    [CreatedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_dbo.emp_Positions] PRIMARY KEY ([POSCode])
)

CREATE TABLE [dbo].[emp_EmployeeTypes] (
    [EMPType] [nvarchar](2) NOT NULL,
    [Name] [nvarchar](256) NOT NULL,
    [SEQID] [int] NOT NULL,
	[RecordState] [int] NOT NULL,
    [Order] [int],
    [ModifiedBy] [nvarchar](256),
    [ModifiedDate] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](256),
    [CreatedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_dbo.emp_EmployeeTypes] PRIMARY KEY ([EMPType])
)
CREATE INDEX [IX_SEQID] ON [dbo].[emp_EmployeeTypes]([SEQID])

CREATE TABLE [dbo].[emp_Sequences] (
    [SEQID] [int] NOT NULL IDENTITY,
    [CurrentValue] [bigint] NOT NULL,
    [ModifiedBy] [nvarchar](256),
    [ModifiedDate] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](256),
    [CreatedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_dbo.emp_Sequences] PRIMARY KEY ([SEQID])
)

CREATE TABLE [dbo].[emp_SupervisingEmployees] (
    [EMPCode] [nvarchar](16) NOT NULL,
    [SupervisorCode] [nvarchar](16) NOT NULL,
    CONSTRAINT [PK_dbo.emp_SupervisingEmployees] PRIMARY KEY ([EMPCode], [SupervisorCode])
)
CREATE INDEX [IX_EMPCode] ON [dbo].[emp_SupervisingEmployees]([EMPCode])
CREATE INDEX [IX_SupervisorCode] ON [dbo].[emp_SupervisingEmployees]([SupervisorCode])

CREATE TABLE [dbo].[emp_EmployeeInPositions] (
	[ID] [int] NOT NULL IDENTITY,
    [EMPCode] [nvarchar](16) NOT NULL,
    [COMCode] [nvarchar](8) NOT NULL,
	[DEPTCode] [nvarchar](8) NULL,
	[TEAMCode] [nvarchar](8) NULL,
	[POSCode] [nvarchar](8) NOT NULL,
	[CropTitle] [nvarchar](256) NULL,
	[IsPrimary] [bit] NOT NULL,
    [ModifiedBy] [nvarchar](256),
    [ModifiedDate] [datetime] NOT NULL,
    [CreatedBy] [nvarchar](256),
    [CreatedDate] [datetime] NOT NULL,
    CONSTRAINT [PK_dbo.emp_EmployeeInPositions] PRIMARY KEY ([ID]),
	CONSTRAINT [UC_dbo.emp_EmployeeInPositions] UNIQUE ([EMPCode], [COMCode], [DEPTCode], [TEAMCode], [POSCode])
)
CREATE INDEX [IX_EMPCode] ON [dbo].[emp_EmployeeInPositions]([EMPCode])
CREATE INDEX [IX_POSCode] ON [dbo].[emp_EmployeeInPositions]([POSCode])
CREATE INDEX [IX_COMCode] ON [dbo].[emp_EmployeeInPositions]([COMCode])
CREATE INDEX [IX_DEPTCode] ON [dbo].[emp_EmployeeInPositions]([DEPTCode])
CREATE INDEX [IX_TEAMCode] ON [dbo].[emp_EmployeeInPositions]([TEAMCode])





ALTER TABLE [dbo].[emp_ProfilePics] ADD CONSTRAINT [FK_dbo.emp_ProfilePics_dbo.emp_Employees_EMPCode] FOREIGN KEY ([EMPCode]) REFERENCES [dbo].[emp_Employees] ([EMPCode])
ALTER TABLE [dbo].[emp_Companies] ADD CONSTRAINT [FK_dbo.emp_Companies_dbo.emp_Companies_ParentCOMCode] FOREIGN KEY ([ParentCOMCode]) REFERENCES [dbo].[emp_Companies] ([COMCode])
ALTER TABLE [dbo].[emp_Departments] ADD CONSTRAINT [FK_dbo.emp_Departments_dbo.emp_Companies_COMCode] FOREIGN KEY ([COMCode]) REFERENCES [dbo].[emp_Companies] ([COMCode])
ALTER TABLE [dbo].[emp_Departments] ADD CONSTRAINT [FK_dbo.emp_Departments_dbo.emp_Departments_ParentDEPTCode] FOREIGN KEY ([ParentDEPTCode]) REFERENCES [dbo].[emp_Departments] ([DEPTCode])
ALTER TABLE [dbo].[emp_Teams] ADD CONSTRAINT [FK_dbo.emp_Teams_dbo.emp_Departments_DEPTCode] FOREIGN KEY ([DEPTCode]) REFERENCES [dbo].[emp_Departments] ([DEPTCode])
ALTER TABLE [dbo].[emp_EmployeeTypes] ADD CONSTRAINT [FK_dbo.emp_EmployeeTypes_dbo.emp_Sequences_SEQID] FOREIGN KEY ([SEQID]) REFERENCES [dbo].[emp_Sequences] ([SEQID])

ALTER TABLE [dbo].[emp_SupervisingEmployees] ADD CONSTRAINT [FK_dbo.emp_SupervisingEmployees_dbo.emp_Employees_EMPCode] FOREIGN KEY ([EMPCode]) REFERENCES [dbo].[emp_Employees] ([EMPCode])
ALTER TABLE [dbo].[emp_SupervisingEmployees] ADD CONSTRAINT [FK_dbo.emp_SupervisingEmployees_dbo.emp_Employees_SupervisorCode] FOREIGN KEY ([SupervisorCode]) REFERENCES [dbo].[emp_Employees] ([EMPCode])


ALTER TABLE [dbo].[emp_EmployeeInPositions] ADD CONSTRAINT [FK_dbo.emp_EmployeeInPositions_dbo.emp_Employees_EMPCode] FOREIGN KEY ([EMPCode]) REFERENCES [dbo].[emp_Employees] ([EMPCode])
ALTER TABLE [dbo].[emp_EmployeeInPositions] ADD CONSTRAINT [FK_dbo.emp_EmployeeInPositions_dbo.emp_Positions_POSCode] FOREIGN KEY ([POSCode]) REFERENCES [dbo].[emp_Positions] ([POSCode])
ALTER TABLE [dbo].[emp_EmployeeInPositions] ADD CONSTRAINT [FK_dbo.emp_EmployeeInPositions_dbo.emp_Departments_DEPTCode] FOREIGN KEY ([DEPTCode]) REFERENCES [dbo].[emp_Departments] ([DEPTCode])
ALTER TABLE [dbo].[emp_EmployeeInPositions] ADD CONSTRAINT [FK_dbo.emp_EmployeeInPositions_dbo.emp_Teams_TEAMCode] FOREIGN KEY ([TEAMCode]) REFERENCES [dbo].[emp_Teams] ([TEAMCode])
ALTER TABLE [dbo].[emp_EmployeeInPositions] ADD CONSTRAINT [FK_dbo.emp_EmployeeInPositions_dbo.emp_Companies_COMCode] FOREIGN KEY ([COMCode]) REFERENCES [dbo].[emp_Companies] ([COMCode])




-- optional --
--  Audit Log --
CREATE TABLE [dbo].[TrackChangeLogs](
	[ID] [bigint] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Date] [datetime] NOT NULL,
	[Table] [nvarchar](256) NOT NULL,
	[User] [nvarchar](256) NULL,
	[Action] [int] NOT NULL,
	[ActionDesc] [nvarchar](256) NOT NULL,
	[ObjectType] [nvarchar](800) NULL,
	[SerializedData] [ntext] NULL
) 
GO

ALTER TABLE [dbo].[TrackChangeLogs] ADD  DEFAULT ('') FOR [ActionDesc]
GO

